package org.kl.auditsupervision.web.fnd;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javax.servlet.ServletOutputStream;
import javax.servlet.ServletRequest;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import org.activiti.engine.runtime.ProcessInstance;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.joda.time.DateTime;
import org.kl.auditsupervision.dto.audit.RiskDimensionStatistic;
import org.kl.auditsupervision.entity.fnd.AuditProDtl;
import org.kl.auditsupervision.entity.fnd.Employee;
import org.kl.auditsupervision.entity.fnd.ZgCs;
import org.kl.auditsupervision.service.fnd.AuditProDtlService;
import org.kl.auditsupervision.service.fnd.AuditProService;
import org.kl.auditsupervision.service.fnd.OrgService;
import org.kl.auditsupervision.service.fnd.ZgCsService;
import org.kl.bf.entity.basic.User;
import org.kl.bf.entity.easyui.EasyuiUtils;
import org.kl.bf.service.basic.DBProcessExecuteService;
import org.kl.bf.utils.KLServlets;
import org.kl.bf.web.base.BasicController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
import org.springside.modules.web.MediaTypes;

import freemarker.template.utility.StringUtil;

/**
 *  
 * @since  2016-08-02 17:07:38
 * @see 审计明细
 */
@SuppressWarnings("deprecation")
@Controller
@RequestMapping(value = "/fnd/auditProDtl")
public class AuditProDtlController extends BasicController {

	@Autowired
	private AuditProDtlService auditProDtlService;
	@Autowired
	private ZgCsService zgCsService;
	@Autowired
	private OrgService orgService;
	@Autowired
	private DBProcessExecuteService dBProcessExecuteService;
	@Autowired
	private AuditProService auditProService;
	
	
	/**
	 * 审计选择框
	 * @param pageNumber
	 * @param pageSize
	 * @param sortType
	 * @param orderType
	 * @param model
	 * @param request
	 * @return
	 */
	@RequestMapping(value = "dataGrids", produces = MediaTypes.JAVASCRIPT_UTF_8)
	@ResponseBody
	public String dataGrids(@RequestParam(value = "pageNumber", defaultValue = "1") int pageNumber,
			@RequestParam(value = "rows", defaultValue = PAGE_SIZE) int pageSize,
			@RequestParam(value = "sort", defaultValue = "auditEvaluationNo,auditFindingNo,auditSuggestionNo,id") String sortType,
			@RequestParam(value = "order", defaultValue = "asc,asc,asc,asc") String orderType, 
			@RequestParam(value = "businessKey", defaultValue = "-1") String businessKey, Model model, ServletRequest request) {
		try{
			Map<String, Object> searchParams = KLServlets.getParametersStartingWith(request, SEARCH_PREFIX);
			Page<AuditProDtl> auditProDtl;
			//在审批页面，只显示审批的那条记录
			if (!businessKey.equals("-1")) {
				searchParams.put("EQ_id", businessKey);
				auditProDtl = auditProDtlService.getPage(searchParams, pageNumber, pageSize, sortType,orderType);
			}else{
				//queryFilter(searchParams,"training");
				auditProDtl =auditProDtlService.getPage(searchParams, pageNumber, pageSize, sortType,orderType);
			}
			return EasyuiUtils.buildEasyuiGridJsonString(auditProDtl.getContent(), (int) auditProDtl.getTotalElements());
		}catch(Exception e){
			e.printStackTrace();
			return "数据加载失败";
		}
			
	}
	
	
	 
	@RequestMapping(value = "riskDimensionStatisticList",method = RequestMethod.GET)
	public String RiskDimensionStatisticList(Model model) {
		return "fnd/riskDimensionStatisticList";
	}
	
	
	/*@RequestMapping(method = RequestMethod.GET)
	public String list(Model model) {
		return "fnd/auditProDtlList";
	}*/
	
	
	@RequestMapping(value = "ZRLeaderAssign")
	public String ZRLeaderAssign(@RequestParam(value = "id", defaultValue = "") String id,
			@RequestParam(value = "businessKey", defaultValue = "") String businessKey, Model model,
			HttpServletRequest request) {
		AuditProDtl auditProDtl=auditProDtlService.getOne(businessKey);
		model.addAttribute("auditProDtl", auditProDtl);
		return "fnd/auditProDtlList_ZRLeaderAssign";
	}
	
	
	@RequestMapping(value = "ZRMake")
	public String ZRMake(@RequestParam(value = "id", defaultValue = "") String id,
			@RequestParam(value = "businessKey", defaultValue = "") String businessKey, 
			@RequestParam(value = "isEdit", defaultValue = "") String isEdit,Model model,
			HttpServletRequest request) {
		model.addAttribute("isEdit", 0);
		AuditProDtl auditProDtl=auditProDtlService.getOne(businessKey);
		model.addAttribute("auditProDtl", auditProDtl);
		model.addAttribute("auditSuggestion", auditProDtl.getAuditSuggestion().replaceAll("(\r\n|\r|\n|\n\r)", "<br>"));
		model.addAttribute("auditFinding", auditProDtl.getAuditFinding().replaceAll("(\r\n|\r|\n|\n\r)", "<br>")); 
		return "fnd/auditProDtlList_ZRMake";
	}
	
	@RequestMapping(value = "ZRMakeEdit")
	public String ZRMakeEdit(@RequestParam(value = "id", defaultValue = "") String id,
			@RequestParam(value = "businessKey", defaultValue = "") String businessKey, 
			@RequestParam(value = "isEdit", defaultValue = "") String isEdit,Model model,
			HttpServletRequest request) {
		model.addAttribute("isEdit", 1);
		AuditProDtl auditProDtl=auditProDtlService.getOne(businessKey);
		model.addAttribute("auditProDtl", auditProDtl);
		model.addAttribute("auditSuggestion", auditProDtl.getAuditSuggestion().replaceAll("(\r\n|\r|\n|\n\r)", "<br>"));
		model.addAttribute("auditFinding", auditProDtl.getAuditFinding().replaceAll("(\r\n|\r|\n|\n\r)", "<br>")); 
		return "fnd/auditProDtlList_ZRMake";
	}

	@RequestMapping(value = "ZRExe")
	public String ZRExe(@RequestParam(value = "id", defaultValue = "") String id,
			@RequestParam(value = "businessKey", defaultValue = "") String businessKey, Model model,
			HttpServletRequest request) {
		model.addAttribute("isEdit", 0);
		AuditProDtl auditProDtl=auditProDtlService.getOne(businessKey);
		model.addAttribute("auditProDtl", auditProDtl);
		model.addAttribute("auditSuggestion", auditProDtl.getAuditSuggestion().replaceAll("(\r\n|\r|\n|\n\r)", "<br>"));
		model.addAttribute("auditFinding", auditProDtl.getAuditFinding().replaceAll("(\r\n|\r|\n|\n\r)", "<br>")); 
		return "fnd/auditProDtlList_ZRExe";
	}
	
	@RequestMapping(value = "ZRExeEdit")
	public String ZRExeEdit(@RequestParam(value = "id", defaultValue = "") String id,
			@RequestParam(value = "businessKey", defaultValue = "") String businessKey, Model model,
			HttpServletRequest request) {
		model.addAttribute("isEdit", 1);
		AuditProDtl auditProDtl=auditProDtlService.getOne(businessKey);
		model.addAttribute("auditProDtl", auditProDtl);
		model.addAttribute("auditSuggestion", auditProDtl.getAuditSuggestion().replaceAll("(\r\n|\r|\n|\n\r)", "<br>"));
		model.addAttribute("auditFinding", auditProDtl.getAuditFinding().replaceAll("(\r\n|\r|\n|\n\r)", "<br>")); 
		return "fnd/auditProDtlList_ZRExe";
	}
	
	
	@RequestMapping(value = "ASEva")
	public String ASEva(@RequestParam(value = "id", defaultValue = "") String id,
			@RequestParam(value = "businessKey", defaultValue = "") String businessKey, Model model,
			HttpServletRequest request) {
		model.addAttribute("isEdit", 1);
		AuditProDtl auditProDtl=auditProDtlService.getOne(businessKey);
		model.addAttribute("auditProDtl", auditProDtl);
		return "fnd/auditProDtlList_ASEva";
	}
	
	@RequestMapping(value = "StatisticList")
	public String StatisticList(@RequestParam(value = "id", defaultValue = "") String id,
			@RequestParam(value = "businessKey", defaultValue = "") String businessKey, Model model,
			HttpServletRequest request) {
		return "fnd/auditProDtlStatisticList";
	}
	
	@RequestMapping(value = "dataGrid", produces = { MediaTypes.TEXT_HTML_UTF_8, MediaTypes.JSON_UTF_8 })
	@ResponseBody
	public String dataGrid(@RequestParam(value = "auditId", defaultValue = "") String auditId,
			@RequestParam(value = "pageNumber", defaultValue = "1") int pageNumber,
			@RequestParam(value = "rows", defaultValue = PAGE_SIZE) int pageSize,
			@RequestParam(value = "sort", defaultValue = "auditEvaluationNo,auditFindingNo,auditSuggestionNo,id") String sortType,
			@RequestParam(value = "order", defaultValue = "asc,asc,asc,asc") String orderType, 
			@RequestParam(value = "businessKey", defaultValue = "-1") String businessKey,Model model, ServletRequest request) {
		Map<String, Object> searchParams = KLServlets.getParametersStartingWith(request, SEARCH_PREFIX);
		if(StringUtils.isNotEmpty(auditId)){
			searchParams.put("EQ_auditPro.id", auditId);
		}		
		Page<AuditProDtl> auditProDtl;
		//在审批页面，只显示审批的那条记录
		if (!businessKey.equals("-1")) {
			searchParams.put("EQ_id", businessKey);
		}else{
			if (!isPermission("auditProDtl" + ":allQuery")) {
				Employee employee = userService.getUser(getCurrentUserId()).getEmployee();
				searchParams.put("EQ_employee.id", employee.getId());
			}
		}
		auditProDtl = auditProDtlService.getPage(searchParams, pageNumber, pageSize, sortType,orderType);
		return EasyuiUtils.buildEasyuiGridJsonString(auditProDtl.getContent(), (int) auditProDtl.getTotalElements());
	}
	
	
	@RequestMapping(value = "dataGridStatistic", produces = { MediaTypes.TEXT_HTML_UTF_8, MediaTypes.JSON_UTF_8 })
	@ResponseBody
	public String dataGridStatistic(
			@RequestParam(value = "pageNumber", defaultValue = "1") int pageNumber,
			@RequestParam(value = "rows", defaultValue = PAGE_SIZE) int pageSize,
			@RequestParam(value = "sort", defaultValue = "auditPro.auditName,auditEvaluationNo,auditFindingNo,auditSuggestionNo,id") String sortType,
			@RequestParam(value = "order", defaultValue = "asc,asc,asc,asc,asc") String orderType,Model model, ServletRequest request) {
		Map<String, Object> searchParams = KLServlets.getParametersStartingWith(request, SEARCH_PREFIX);
		
		if(isPermission("auditProDtl:allQuery")){
	    	
	    }else if(isPermission("auditProDtl:deptQuery")){
	    	String orgId="";
	    	orgId=BasicController.getCurrentUserDeptId();
	    	searchParams.put("EQ_org.id", orgId);
	    }else{
	    	User user = userService.getUser(BasicController.getCurrentUserId());
	    	searchParams.put("EQ_employee.id", user.getEmployee().getId());
	    }
		Page<AuditProDtl> auditProDtl= auditProDtlService.getPage(searchParams, pageNumber, pageSize, sortType,orderType);
		return EasyuiUtils.buildEasyuiGridJsonString(auditProDtl.getContent(), (int) auditProDtl.getTotalElements());
	}
	
	//风险维度统计
	@RequestMapping(value = "riskDimensionStatistic" , produces = { MediaTypes.TEXT_HTML_UTF_8, MediaTypes.JSON_UTF_8 })
	@ResponseBody
	public String RiskDimensionStatisticDataGrid(@RequestParam(value = "pageNumber", defaultValue = "1") int pageNumber,
			@RequestParam(value = "rows", defaultValue = PAGE_SIZE) int pageSize,
			@RequestParam(value = "sort", defaultValue = "empName") String sortType,
			@RequestParam(value = "order", defaultValue = "asc") String orderType, Model model, ServletRequest request) {
		try{
			String startYear=request.getParameter("startYear");
		    String endYear=request.getParameter("endYear");	
		    String type=request.getParameter("type");	  		    
		    List<RiskDimensionStatistic> riskDimensionStatisticList = auditProDtlService.getRiskDimensionStatistic(startYear,endYear,type,pageNumber,pageSize,sortType,orderType);
			int count=auditProDtlService.getRiskDimensionStatisticCount(startYear,endYear,type);
			return EasyuiUtils.buildEasyuiGridJsonString(riskDimensionStatisticList,count);
		}catch(Exception e){
			e.printStackTrace();
			return ajaxMsg("加载数据失败！", false, null);
		}
	}

	/*@RequestMapping(value = "editSuperintendent", method = RequestMethod.POST, produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String editSuperintendent(@Valid @ModelAttribute("auditProDtl") AuditProDtl auditProDtl,
			@RequestParam(value = "detailInserted", required = false) String detailInserted,
			@RequestParam(value = "detailUpdated", required = false) String detailUpdated, 
			@RequestParam(value = "detailDeleted", required = false) String detailDeleted, BindingResult result) {
		if(result.hasErrors()){
			return ajaxMsg(bindingMsg(result), false, null);
		}
		try{
			if(auditProDtl.getOrg()!=null&&StringUtils.isBlank(auditProDtl.getOrg().getId())){
				return ajaxMsg("保存失败:部门不能为空" , false, null);
			}
			if(StringUtils.isBlank(auditProDtl.getSuperintendent().getId())){
				return ajaxMsg("保存失败:内控监督人不能为空" , false, null);
			}
			auditProDtlService.save(auditProDtl);
		    return ajaxMsg("保存成功", true, auditProDtl);
		}catch(Exception e){
			e.printStackTrace();
			return ajaxMsg("保存失败:" + e.getMessage(), false, null);
		}
	}*/
	
	@RequestMapping(value = "editEmployee", method = RequestMethod.POST, produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String editEmployee(@Valid @ModelAttribute("auditProDtl") AuditProDtl auditProDtl,
			@RequestParam(value = "detailInserted", required = false) String detailInserted,
			@RequestParam(value = "detailUpdated", required = false) String detailUpdated, 
			@RequestParam(value = "detailDeleted", required = false) String detailDeleted, BindingResult result) {
		if(result.hasErrors()){
			return ajaxMsg(bindingMsg(result), false, null);
		}
		try{
			if(auditProDtl.getOrg()!=null&&StringUtils.isBlank(auditProDtl.getOrg().getId())){
				return ajaxMsg("保存失败:部门不能为空" , false, null);
			}
			if(StringUtils.isBlank(auditProDtl.getEmployee().getId())){
				return ajaxMsg("保存失败:责任人不能为空" , false, null);
			}
			User user = userService.findUserByEmployeeId(auditProDtl.getEmployee().getId());
			String message="";
			message=dBProcessExecuteService.ChangeRiskAssignee(auditProDtl.getId(),user.getId() );
			if(StringUtils.isEmpty(message)){
				auditProDtlService.save(auditProDtl);
			}else{
				return ajaxMsg("保存失败:" + message, false, null);
			}
		    return ajaxMsg("保存成功", true, null);
		}catch(Exception e){
			e.printStackTrace();
			return ajaxMsg("保存失败:" + e.getMessage(), false, null);
		}
	}
	
	
	
	@RequestMapping(value = "update", method = RequestMethod.POST, produces = { MediaTypes.TEXT_HTML_UTF_8,
			MediaTypes.JSON_UTF_8 })
	@ResponseBody
	public String update(@Valid @ModelAttribute("auditProDtl") AuditProDtl auditProDtl,
			@RequestParam(value = "detailInserted", required = false) String detailInserted,
			@RequestParam(value = "detailUpdated", required = false) String detailUpdated, 
			@RequestParam(value = "detailDeleted", required = false) String detailDeleted, BindingResult result) {
		if(result.hasErrors()){
			return ajaxMsg(bindingMsg(result), false, null);
		}
		try{
			if(auditProDtl.getOrg()!=null&&StringUtils.isBlank(auditProDtl.getOrg().getId())){
				return ajaxMsg("保存失败:部门不能为空" , false, null);
			}
			if(StringUtils.isBlank(auditProDtl.getSuperintendent().getId())){
				return ajaxMsg("保存失败:内控监督人不能为空" , false, null);
			}
			auditProDtlService.save(auditProDtl);
		    return ajaxMsg("保存成功", true, auditProDtlService.getOne(auditProDtl.getId()));
		}catch(Exception e){
			e.printStackTrace();
			return ajaxMsg("保存失败:" + e.getMessage(), false, null);
		}
	}
	
	/**
	 * 申请审批流程启动
	 * 
	 * @param id
	 * @return
	 */
	@RequiresPermissions({ "auditProDtl:update" })
	@RequestMapping(value = "apply/{id}", produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String apply(@PathVariable("id") String id) {
		if (StringUtils.isNotBlank(id)) {
			try {
				ProcessInstance processInstance=null;
				AuditProDtl rpa = auditProDtlService.getOne(id);
				Map<String, Object> variables = new HashMap<String, Object>();
				if (rpa != null) {
					processInstance=auditProDtlService.startWorkflow(rpa, variables);
				}
				return ajaxMsg("启动流程成功,processId" + processInstance.getId(), true, processInstance.getProcessInstanceId());
			} catch (Exception e) {
				e.printStackTrace();
				return ajaxMsg("申请审批流程启动失败：" + e.getMessage(), false, null);
			}
		} else {
			return ajaxMsg("申请审批流程启动失败：请刷新页面重试，申请标识未找到", false, null);
		}
	}
	
	@RequiresPermissions(value = "auditProDtl:delete")
	@RequestMapping(value = "delete/{id}", produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String delete(@PathVariable("id") String id, RedirectAttributes redirectAttributes) {
		try {
			auditProDtlService.delete(id);
			return ajaxMsg("删除成功", true, null);
		} catch (Exception e) {
			e.printStackTrace();
			return ajaxMsg("删除失败:" + e.getMessage(), false, null);
		}
	}
	
	@RequiresPermissions({ "auditProDtl:delete" })
	@RequestMapping(value = "multiDelete", produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String multiDelete(@RequestParam(value = "ids", defaultValue = "") String ids) {
		String msg = "";
		try {
			msg = auditProDtlService.multiDelete(ids);
		} catch (Exception e) {
			e.printStackTrace();
			return ajaxMsg("删除失败:" + e.getMessage(), false, null);
		}
		return ajaxMsg("删除成功:" + msg, true, null);
	}
	
	@RequiresPermissions("auditProDtl:export")
	@RequestMapping(value = "excelExport", produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String excelExport(
			@RequestParam(value = "pageNumber", defaultValue = "1") int pageNumber,
			@RequestParam(value = "sort", defaultValue = "id") String sortType,
			@RequestParam(value = "order", defaultValue = "desc") String orderType, Model model, ServletRequest request,
			HttpServletResponse response) {
		Map<String, Object> searchParams = KLServlets.getParametersStartingWith(request, SEARCH_PREFIX);
		List<AuditProDtl> auditProDtl = auditProDtlService.searchAll(searchParams, sortType, orderType);
		//super.export(AuditProDtl.class, response, auditProDtl, "审计明细表");
		List<String> idList=new ArrayList<String>();
		for(AuditProDtl entity:auditProDtl){
			if(!idList.contains(entity.getAuditPro().getId())){
				idList.add(entity.getAuditPro().getId());
			}
		}
		try{
			ServletOutputStream os=response.getOutputStream();
			response.reset();
			if(auditProDtl==null){
				return ajaxMsg("未找到符合条件的审计明细信息!", false,null);
			}
			//String filename=new String(("【"+auditProDtl.get(0).getAuditPro().getAuditName()+"】整改计划").getBytes("gb2312"),"ISO8859-1")+".xls";
			String filename=new String(("整改计划、证据汇总表"+ new DateTime().toString("yyyyMMddHHmmss")).getBytes("gb2312"),"ISO8859-1")+".xls";
			response.setHeader("Content-disposition", "attachment;filename="+filename);
			response.setContentType("application/msexcel");
			String filePath=((HttpServletRequest) request).getSession().getServletContext().getRealPath("/ExcelExportTemplate/审计整改计划.xls");
			HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(filePath));
			
			
		 
			
			try{
				HSSFSheet sheet=workbook.getSheetAt(0);//获取第一个sheet
				HSSFRow row;
				HSSFCell cell=null;

				int rownum=1;//添加的起始行
				
				//设置样式;  
		        HSSFCellStyle style = workbook.createCellStyle();  
		        //设置底边框;  
		        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
		        //设置底边框颜色;  
		        style.setBottomBorderColor(HSSFColor.BLACK.index);  
		        //设置左边框;  
		        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		        
		        //设置左边框颜色;  
		        style.setLeftBorderColor(HSSFColor.BLACK.index);  
		        //设置右边框;  
		        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		        //设置右边框颜色;  
		        style.setRightBorderColor(HSSFColor.BLACK.index);  
		        //设置顶边框;  
		        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
		        //设置顶边框颜色;  
		        style.setTopBorderColor(HSSFColor.BLACK.index);  
		        //设置自动换行;  
		        style.setWrapText(true);  
		        //设置水平对齐的样式为居中对齐;  
		        style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);  
		        //设置垂直对齐的样式为居中对齐;  
		        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    
		        
		        //统一字体为宋体12号
		        HSSFFont font=workbook.createFont();
		        font.setFontName("宋体");
		        font.setFontHeightInPoints((short)10);
		        style.setFont(font);
		        
		        //设定统一的前景色
		        style.setFillForegroundColor(HSSFColor.WHITE.index);
				style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				
				//设定无前景色的格式
				HSSFCellStyle whiteStyle=workbook.createCellStyle();
				whiteStyle.cloneStyleFrom(style);
				whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);
				whiteStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		        
		        //文本格式
				HSSFCellStyle css=workbook.createCellStyle();
				css.cloneStyleFrom(style);
				DataFormat format=workbook.createDataFormat();
				css.setDataFormat(format.getFormat("@"));
				
				//“自定义yyyy/mm/dd”格式
				HSSFCellStyle dateStyle=workbook.createCellStyle();
				dateStyle.cloneStyleFrom(style);
				DataFormat format2=workbook.createDataFormat();
				dateStyle.setDataFormat(format2.getFormat("yyyy/mm/dd"));
				
				//灰色前景色 常规格式
				HSSFCellStyle grayStyle=workbook.createCellStyle();
				grayStyle.cloneStyleFrom(style);
				grayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
				grayStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				
				//灰色前景色 文本格式
				HSSFCellStyle grayTextStyle=workbook.createCellStyle();
				grayTextStyle.cloneStyleFrom(css);
				grayTextStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
				grayTextStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				
				//红色字体  常规格式
				HSSFCellStyle redFontStyle=workbook.createCellStyle();
				redFontStyle.cloneStyleFrom(style);
				HSSFFont redfont=workbook.createFont();
				redfont.setFontName("宋体");
				redfont.setFontHeightInPoints((short)12);
				redfont.setColor(HSSFColor.RED.index);
				redfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				redFontStyle.setFont(redfont);
				
				 //红色字体  文本格式
				HSSFCellStyle redTextStyle=workbook.createCellStyle();
				redTextStyle.cloneStyleFrom(redFontStyle);
				DataFormat format3=workbook.createDataFormat();
				redTextStyle.setDataFormat(format3.getFormat("@"));
		        
				Iterator<AuditProDtl> iterator=auditProDtl.iterator();
				
				//格式化日期
				SimpleDateFormat format4=new SimpleDateFormat("yyyyMMdd");
				
				
			for(String auditProId:idList){
				auditProDtl=auditProDtlService.findByAuditProId(auditProId);
				
				Set pjSet=new TreeSet<String>();//统计评价条数
				for(AuditProDtl apd:auditProDtl){
				//	String evaString=apd.getAuditFindingEva();
					String evaString=apd.getAuditEvaluationNo();
					if(StringUtils.isNotEmpty(evaString))
						pjSet.add(evaString);
				}
				 //循环评价
				Iterator pjIt=pjSet.iterator();
				int btRowNum=rownum;
				while(pjIt.hasNext()){
					int pjRownum=rownum;//评价开始合并单元格的行号
					Object evaNoString=pjIt.next();
					List<AuditProDtl> evaList=auditProDtlService.findByAuditProIdAndAuditEvaluationNo(auditProId, evaNoString.toString());//findByAuditProIdAndAuditFindingEva(auditProId,evaNoString.toString());//审计发现数据
					AuditProDtl pjEntity=evaList.get(0);
					Set fxSet=new TreeSet<String>();//统计每条评价的发现数量
					for(AuditProDtl apd:evaList){
						String finString=apd.getAuditFindingNo();
						if(StringUtils.isNotEmpty(finString))
							fxSet.add(finString);
					}
					
					Iterator fxIt=fxSet.iterator();
					if(fxSet.size()==0){
						//没有发现
						    row=sheet.createRow(rownum);
							//序号
							myCreateCell(0,rownum+"", row, cell, style);
							//标题
							myCreateCell(1,auditProService.getOne(auditProId).getAuditName(), row, cell, style);
							//审计监察评价
							myCreateCell(2,pjEntity!=null?pjEntity.getAuditEvaluation():"", row, cell, style);
							
							//审计发现
							myCreateCell(3, "", row, cell, style);
							//审计建议
							myCreateCell(4, "", row, cell, style);
							//整改措施
							myCreateCell(5,"", row, cell, style);
							//整改目标
							myCreateCell(6,"", row, cell, style);
							//整改证据
							myCreateCell(7,"", row, cell, style);
							//责任部门
							myCreateCell(8,pjEntity!=null?pjEntity.getOrg().getOrgName():"", row, cell, style);
							//整改计划实际提交时间
							myCreateCell(9,"", row, cell, style);
							//整改结果实际提交时间
							myCreateCell(10,"", row, cell, style);
							//责任人
							myCreateCell(11,pjEntity!=null&&pjEntity.getEmployee()!=null?pjEntity.getEmployee().getTrueName():"", row, cell, style);
							myCreateCell(12,"" , row, cell, style);
							rownum++;
					}else{
						while(fxIt.hasNext()){
							int fxRownum=rownum;//发现开始合并单元格的行号
							Object finString=fxIt.next();
							List<AuditProDtl> sugList=auditProDtlService.findByAuditProIdAndAuditEvaluationNoAndAuditFindingNo(auditProId,evaNoString.toString(), finString.toString());//审计建议数据
							AuditProDtl fxEntity=sugList.get(0);
									for(AuditProDtl ap:sugList){//循环建议
										int jyRownum=rownum;
										List<ZgCs> zgcsList=zgCsService.findByAuditProDtlId(ap.getId());
										if(zgcsList.size()==0){//没有整改措施  
											 row=sheet.createRow(rownum);
												//序号
												myCreateCell(0,rownum+"", row, cell, style);
												//标题
												myCreateCell(1,auditProService.getOne(auditProId).getAuditName(), row, cell, style);
												//审计监察评价
												myCreateCell(2,StringUtils.isNotBlank(ap.getAuditEvaluation())?ap.getAuditEvaluation():"", row, cell, style);
												//审计发现
												myCreateCell(3,StringUtils.isNoneBlank(ap.getAuditFinding())?ap.getAuditFinding():"", row, cell, style);
												//审计建议
												myCreateCell(4,StringUtils.isNoneBlank(ap.getAuditSuggestion())?ap.getAuditSuggestion():"", row, cell, style);
												//整改措施
												myCreateCell(5,"", row, cell, style);
												//整改目标
												myCreateCell(6,"", row, cell, style);
												//整改证据
												myCreateCell(7,"", row, cell, style);
												//责任部门
												myCreateCell(8,ap.getOrg()!=null?ap.getOrg().getOrgName():"", row, cell, style);
												//整改计划实际提交时间
												myCreateCell(9,"", row, cell, style);
												//整改结果实际提交时间
												myCreateCell(10,"", row, cell, style);
												//责任人
												myCreateCell(11,ap!=null&&ap.getEmployee()!=null?ap.getEmployee().getTrueName():"", row, cell, style);
												
												myCreateCell(12,String.valueOf(ap.getSystemCount()==null?"":ap.getSystemCount()) , row, cell, style);
												rownum++;
										}else {
												for(ZgCs cs:zgcsList){
													    row=sheet.createRow(rownum);
														//序号
														myCreateCell(0,rownum+"", row, cell, style);
														//标题
														myCreateCell(1,auditProService.getOne(auditProId).getAuditName(), row, cell, style);
														//审计监察评价
														myCreateCell(2,StringUtils.isNoneBlank(ap.getAuditEvaluation())?ap.getAuditEvaluation():"", row, cell, style);
														//审计发现
														myCreateCell(3,StringUtils.isNotBlank(ap.getAuditFinding())?ap.getAuditFinding():"", row, cell, style);
														//审计建议
														myCreateCell(4,StringUtils.isNoneBlank(ap.getAuditSuggestion())?ap.getAuditSuggestion():"", row, cell, style);
														//整改措施
														myCreateCell(5,StringUtils.isNotBlank(cs.getZgcs())?cs.getZgcs():"", row, cell, style);
														//整改目标
														myCreateCell(6,StringUtils.isNotBlank(cs.getZgmb())?cs.getZgmb():"", row, cell, style);
														//整改证据
														myCreateCell(7,StringUtils.isNotBlank(cs.getZgEvidence())?cs.getZgEvidence():"", row, cell, style);
														//责任部门
														myCreateCell(8,ap.getOrg()!=null?ap.getOrg().getOrgName():"", row, cell, style);
														//整改计划实际提交时间
														myCreateCell(9,ap.getZgPlanSubSjTime()!=null?ap.getZgPlanSubSjTime().toString():"", row, cell, style);
														//整改结果实际提交时间
														myCreateCell(10,ap.getZgResultSubSjTime()!=null?ap.getZgResultSubSjTime().toString():"", row, cell, style);
														//责任人
														myCreateCell(11,ap!=null&&ap.getEmployee()!=null?ap.getEmployee().getTrueName():"", row, cell, style);
														myCreateCell(12,String.valueOf(ap.getSystemCount()==null?"":ap.getSystemCount()) , row, cell, style);
														rownum++;
												 }//措施循环结束
										}
										sheet.addMergedRegion(new Region( jyRownum, (short) 4,rownum-1, (short) 4)); 
										 sheet.addMergedRegion(new Region( jyRownum, (short) 8,rownum-1, (short) 8));
										 sheet.addMergedRegion(new Region( jyRownum, (short) 9,rownum-1, (short) 9)); 
										 sheet.addMergedRegion(new Region( jyRownum, (short) 10,rownum-1, (short) 10));
										 sheet.addMergedRegion(new Region( jyRownum, (short) 11,rownum-1, (short) 11)); 
										 sheet.addMergedRegion(new Region( jyRownum, (short) 12,rownum-1, (short) 12)); 
									}//建议循环结束
							    
							 sheet.addMergedRegion(new Region( fxRownum, (short) 3,rownum-1, (short) 3)); 
							
						}//发现循环结束						
					}
					sheet.addMergedRegion(new Region(pjRownum, (short) 2,rownum-1, (short) 2)); 
				}//评价循环结束
				sheet.addMergedRegion(new Region(btRowNum, (short) 1,rownum-1, (short) 1)); 
				}
				workbook.write(os);
				os.flush();
				os.close();
				
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}catch(IOException e){
			e.printStackTrace();
		}
		return null;
	}
	
	@RequiresPermissions("auditProDtl:export")
	@RequestMapping(value = "statisticExcelExport", produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String statisticExcelExport(@RequestParam(value = "pageNumber", defaultValue = "1") int pageNumber,
			@RequestParam(value = "sort", defaultValue = "auditPro.auditName,auditEvaluationNo,auditFindingNo,auditSuggestionNo,id") String sortType,
			@RequestParam(value = "order", defaultValue = "asc,asc,asc,asc,asc") String orderType,Model model, ServletRequest request,
			HttpServletResponse response) {
		 Map<String, Object> searchParams = KLServlets.getParametersStartingWith(request, SEARCH_PREFIX);
		 if(isPermission("auditProDtl:allQuery")){
		    	
		    }else if(isPermission("auditProDtl:deptQuery")){
		    	String orgId="";
		    	orgId=BasicController.getCurrentUserDeptId();
		    	searchParams.put("EQ_org.id", orgId);
		    }else{
		    	User user = userService.getUser(BasicController.getCurrentUserId());
		    	searchParams.put("EQ_employee.id", user.getEmployee().getId());
		    }
		List<AuditProDtl> auditProDtlList =auditProDtlService.searchAll(searchParams, sortType, orderType);
		
		/*List<AuditProDtl> auditProDtlList2=new ArrayList<AuditProDtl>();
		for(AuditProDtl auditProDtl:auditProDtlList){
			AuditProDtl entity=new AuditProDtl();
			entity.setAuditEvaluation(auditProDtl.getAuditEvaluation());
			entity.setAuditEvaluationNo(auditProDtl.getAuditEvaluationNo());
			entity.setAuditFinding(auditProDtl.getAuditFinding());
			entity.setAuditFindingEva(auditProDtl.getAuditFindingEva());
			entity.setAuditFindingNo(auditProDtl.getAuditFindingNo());
			entity.setAuditPro(auditProDtl.getAuditPro());
			entity.setAuditSuggestion(auditProDtl.getAuditSuggestion());
			entity.setAuditSuggestionNo(auditProDtl.getAuditSuggestionNo());
			entity.setConfirmFlag(auditProDtl.getConfirmFlag());
			entity.setConfirmMan(auditProDtl.getConfirmMan());
			entity.setConfirmTime(auditProDtl.getConfirmTime());
			entity.setEmployee(auditProDtl.getEmployee());
			entity.setIsCxzg(auditProDtl.getIsCxzg());
			entity.setOrg(auditProDtl.getOrg());
			entity.setPlanTimes(auditProDtl.getPlanTimes());
			entity.setProcessInstanceId(auditProDtl.getProcessInstanceId());
			entity.setResultTimes(auditProDtl.getResultTimes());
			entity.setStatus(auditProDtl.getConfirmFlag());
			entity.setSuperintendent(auditProDtl.getSuperintendent()); 
	        
	        entity.setZgPlanSubSjTime(auditProDtl.getZgPlanSubSjTime());
			entity.setZgPlanSubTime(auditProDtl.getZgPlanSubTime());
			entity.setZgResultSubSjTime(auditProDtl.getZgResultSubSjTime());
			entity.setZgResultSubTime(new DateTime(auditProDtl.getZgResultSubTime()).toString("yyyy-MM-dd"));
			auditProDtlList2.add(entity);
		
		}*/
		
		super.export(AuditProDtl.class, response, auditProDtlList, "进度跟踪表");
		return null;
	}
	
	@RequiresPermissions("auditProDtl:export")
	@RequestMapping(value = "riskDimensionStatisticExcelExport", produces = MediaTypes.JSON_UTF_8)
	@ResponseBody
	public String riskDimensionStatisticExcelExport(@RequestParam(value = "pageNumber", defaultValue = "1") int pageNumber,
			@RequestParam(value = "sort", defaultValue = "empName") String sortType,
			@RequestParam(value = "order", defaultValue = "asc") String orderType, Model model, ServletRequest request,
			HttpServletResponse response) {
		String startYear=request.getParameter("startYear");
	    String endYear=request.getParameter("endYear");	
	    String type=request.getParameter("type");	  
		    
		 List<RiskDimensionStatistic> riskDimensionStatisticList = auditProDtlService.getRiskDimensionStatistic(startYear,endYear,type,0,0,sortType,orderType);
		super.export(RiskDimensionStatistic.class, response, riskDimensionStatisticList, "风险维度分析表");
		return null;
	}
	/**
	 * 所有RequestMapping方法调用前的Model准备方法, 实现Struts2
	 * Preparable二次部分绑定的效果,先根据form的id从数据库查出Task对象,再把Form提交的内容绑定到该对象上。
	 * 因为仅update()方法的form中有id属性，因此仅在update时实际执行.
	 */
	@ModelAttribute
	public void init(@RequestParam(value = "id", defaultValue = "-1") String id, Model model) {
		if (!id.equals("-1")) {
			model.addAttribute("auditProDtl", auditProDtlService.findOneAndClear(id));
		}
	}
	
	
	public void myCreateCell(int celnum,String value,HSSFRow row,HSSFCell cell,HSSFCellStyle style){
		cell=row.createCell((short)celnum);
		if(StringUtils.isNotBlank(value)){
			cell.setCellValue(new HSSFRichTextString(value));
		}
		cell.setCellStyle(style);
	}
	 
}



